from abc import ABC, abstractmethod
from typing import List

import pydantic
import sqlalchemy as sa
from fastapi_pagination.bases import AbstractPage
from fastapi_pagination.ext.databases import paginate

from like.dependencies.database import db
from like.utils.urls import UrlUtil
from like.utils.array import ArrayUtil
from .models import medical_operation_classification_version30
from .schemas import (
    MedicalOperationClassificationVersion30ListIn, MedicalOperationClassificationVersion30ExportIn, MedicalOperationClassificationVersion30AddIn, MedicalOperationClassificationVersion30EditIn, MedicalOperationClassificationVersion30Out)


class IMedicalOperationClassificationVersion30Service(ABC):
    """用于存储手术操作分类国家临床版3.0信息的抽象类"""

    @abstractmethod
    async def listBase(self, list_in: MedicalOperationClassificationVersion30ListIn) -> sa.sql.Select:
        pass

    @abstractmethod
    async def list(self, list_in: MedicalOperationClassificationVersion30ListIn) -> AbstractPage[MedicalOperationClassificationVersion30Out]:
        pass

    @abstractmethod    
    async def export_excel(self, export_in: MedicalOperationClassificationVersion30ExportIn) -> str:
        pass    

    @abstractmethod
    async def detail(self, id_: int) -> MedicalOperationClassificationVersion30Out:
        pass

    @abstractmethod
    async def add(self, add_in: MedicalOperationClassificationVersion30AddIn):
        pass

    @abstractmethod
    async def edit(self, edit_in: MedicalOperationClassificationVersion30EditIn):
        pass

    @abstractmethod
    async def delete(self, id_: int):
        pass


class MedicalOperationClassificationVersion30Service(IMedicalOperationClassificationVersion30Service):
    """用于存储手术操作分类国家临床版3.0信息的基类"""

    async def listBase(self, list_in: MedicalOperationClassificationVersion30ListIn) -> sa.sql.Select:
        """用于存储手术操作分类国家临床版3.0信息的基类"""
        columns = [
            medical_operation_classification_version30,
            
            ]
        where = []
        if list_in.id is not None and str(list_in.id):
            where.append(medical_operation_classification_version30.c.id == list_in.id)
        if list_in.code is not None and str(list_in.code):
            where.append(medical_operation_classification_version30.c.code == list_in.code)
        if list_in.name is not None and str(list_in.name):
            where.append(medical_operation_classification_version30.c.name.like(f'%{ list_in.name }%'))
        if list_in.category is not None and str(list_in.category):
            where.append(medical_operation_classification_version30.c.category == list_in.category)
        if list_in.input_options is not None and str(list_in.input_options):
            where.append(medical_operation_classification_version30.c.input_options == list_in.input_options)
        if list_in.create_time is not None and str(list_in.create_time):
            where.append(medical_operation_classification_version30.c.create_time == list_in.create_time)
        if list_in.update_time is not None and str(list_in.update_time):
            where.append(medical_operation_classification_version30.c.update_time == list_in.update_time)
        query = sa.select(*columns).where(*where) \
            .select_from(
            medical_operation_classification_version30
        ).order_by(medical_operation_classification_version30.c.id.desc())
        return query

    async def list(self, list_in: MedicalOperationClassificationVersion30ListIn) -> AbstractPage[MedicalOperationClassificationVersion30Out]:
        """用于存储手术操作分类国家临床版3.0信息的列表"""
        query = await self.listBase(list_in)
        res = await paginate(db, query)
        return res    

    async def export_excel(self, export_in: MedicalOperationClassificationVersion30ExportIn) -> str:    
        """用于存储手术操作分类国家临床版3.0信息的导出Excel"""
        result = await self.listBase(export_in)

        # 计算要导出的记录范围
        offset = (export_in.page_start - 1) * export_in.page_size
        limit = (export_in.page_end - export_in.page_start + 1) * export_in.page_size

        # 修改查询以仅选择所需的记录
        result = result.offset(offset).limit(limit)

        query_result = await db.fetch_all(result)

        # 将查询结果转换为 Pandas DataFrame
        df = pd.DataFrame(query_result, columns=result.selected_columns.keys())

        # 将 DataFrame 转换为 Excel 文件的字节流
        excel_data = BytesIO()
        df.to_excel(excel_data, index=False)
        excel_data.seek(0)  # 将指针移回文件开头

        excel_bytes = excel_data.read()  # 读取字节流内容到字节字符串中
        return base64.b64encode(excel_bytes).decode('utf-8')  # 将字节字符串转换为Base64编码的字符串表示ArrayBuffer


    async def detail(self, id_: int) -> MedicalOperationClassificationVersion30Out:
        """用于存储手术操作分类国家临床版3.0信息的详情"""
        model = await db.fetch_one(
            medical_operation_classification_version30.select().where(
                medical_operation_classification_version30.c.id == id_)
            .limit(1))
        assert model, '数据不存在!'
        res = MedicalOperationClassificationVersion30Out.from_orm(model)
        return res

    async def add(self, add_in: MedicalOperationClassificationVersion30AddIn):
        """用于存储手术操作分类国家临床版3.0信息的新增"""
        await db.execute(medical_operation_classification_version30.insert().values({
            'code': add_in.code,
            'name': add_in.name,
            'category': add_in.category,
            'input_options': add_in.input_options,
        }))

    async def edit(self, edit_in: MedicalOperationClassificationVersion30EditIn):
        """用于存储手术操作分类国家临床版3.0信息的编辑"""
        assert await db.fetch_one(
            medical_operation_classification_version30.select().where(
                medical_operation_classification_version30.c.id == edit_in.id)
            .limit(1)), '数据不存在!'
        await db.execute(
            medical_operation_classification_version30.update().where(medical_operation_classification_version30.c.id == edit_in.id).values({
                'code': edit_in.code,
                'name': edit_in.name,
                'category': edit_in.category,
                'input_options': edit_in.input_options,
            }))

    async def delete(self, id_: int):
        """用于存储手术操作分类国家临床版3.0信息的删除"""
        assert await db.fetch_one(
            medical_operation_classification_version30.select().where(
                medical_operation_classification_version30.c.id == id_)
            .limit(1)), '数据不存在!'
        await db.execute(medical_operation_classification_version30.delete().where(medical_operation_classification_version30.c.id == id_))
        
    @classmethod
    async def instance(cls):
        """实例化"""
        return cls()
